-- This software is provided 'as-is', without any express or implied warranty.  In no event will the authors be held liable for any damages arising from the use of this software.
-- Permission is granted to anyone to use this software for any purpose, including commercial applications, and to alter it and redistribute it freely, subject to the following restrictions:

-- 1. The origin of this software must not be misrepresented;
-- you must not claim that you wrote the original generator/generated software.
-- If you use this software in a product, an acknowledgment in
-- the product documentation would be appreciated.
-- 2. Altered source versions must be plainly marked as such, and must not be misrepresented as being the original software.
-- 3. This notice may not be removed or altered from any source distribution.

-- (AutoProc Team)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO


ALTER PROC [dbo].[get_sp] @tablename varchar (128),@column varchar(128) = NULL
AS
BEGIN
SET NOCOUNT ON;

DECLARE @operator varchar(10)
DECLARE @datatype varchar(100)
DECLARE @length varchar(10)
DECLARE @spname varchar(100)
DECLARE @body nvarchar(max)
DECLARE @body1 nvarchar(max)
DECLARE @body2 nvarchar(max)
DECLARE @selectcollist varchar(max)
DECLARE @licence varchar(MAX)
DECLARE @firstcolumn varchar(MAX)

IF @column is null
	SET @spname = 'Get'+@tablename
ELSE
	SET @spname = 'Get'+@tablename+'By'+@column

SELECT @datatype=data_type,@length=character_maximum_length from information_schema.columns
	WHERE table_name = @tablename and column_name=@column

-- Added for the OVER clause's ORDER BY
SELECT TOP 1 @firstcolumn = column_name
	FROM information_schema.columns
		WHERE table_name = @tablename 
		ORDER BY ordinal_position

SELECT @selectcollist = coalesce(@selectcollist + '        ,','        ') +  '['+ column_name +']'  + char(13) + char(10)
	FROM information_schema.columns
		WHERE table_name = @tablename 
		ORDER BY ordinal_position

SELECT @body=''
SELECT @body1=''
SELECT @body2=''

SELECT @body1 =@body1+'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'''+@spname+''') AND type in (N''P'', N''PC''))'+ char(13) + char(10)
SELECT @body1 =@body1+'DROP PROCEDURE '+@spname+ char(13) + char(10)
EXEC sp_executesql @body1

SELECT @body2 =@body2+'-- =============================================' + char(13) + char(10)
SELECT @body2 =@body2+'-- Author:		AutoProc Team' + char(13) + char(10)
SELECT @body2 =@body2+'-- Create date: ' + convert(varchar(10),getdate(),101) + char(13) + char(10)
SELECT @body2 =@body2+'-- Description:	Retrieves table '+@tablename +'''s details' + char(13) + char(10)
SELECT @body2 =@body2+'-- =============================================' + char(13) + char(10)
SELECT @body2 = @body2 + 'CREATE PROCEDURE ' + @spname + char(13) + char(10)

IF @column is not null
	BEGIN
		SELECT @body2 = @body2 +'(' + char(13) + char(10)
		IF @datatype in('char','varchar','nchar','nvarchar','varbinary','binary')
			SELECT @body2=@body2+' @'+@column+ ' '+@datatype+'('+replace(@length,'-1','max')+')' + char(13) + char(10)
		ELSE
			SELECT @body2=@body2+' @'+@column+ ' '+@datatype + char(13) + char(10)
			SELECT @body2=@body2 + ' ,@_start int = 0' + char(13)+ char(10) + ' ,@_count int = 0' + ')' + char(13)+ char(10);
	END
ELSE
		SELECT @body2=@body2 + ' (@_start int = 0' + char(13)+ char(10) + ' ,@_count int = 0' + ')' + char(13)+ char(10);

SELECT @body2=@body2+'AS' + char(13) + char(10)
SELECT @body2=@body2+'BEGIN' + char(13) + char(10)

-------------------------------------------

SELECT @body2=@body2+'    SET NOCOUNT ON;' + char(13) + char(10)
SELECT @body2=@body2+'    IF @_start = 0 AND @_count = 0' + char(13) + char(10)
SELECT @body2=@body2+'		SELECT' + char(13) + char(10)
SELECT @body2=@body2+ @selectcollist 

IF @column is not null
	BEGIN
		SELECT @body2=@body2+'    FROM ' + '['+ @TABLENAME + ']'+ char(13) + char(10)
		SELECT @body2=@body2+'    WHERE '+ @column +' = @'+@column +' ORDER BY [' + @column + '];'+ char(13) + char(10)
	END
ELSE
BEGIN
	SELECT @body2=@body2+'    FROM ' + '['+ @TABLENAME + '];'+ char(13) + char(10)
END

IF @column is not null
	BEGIN
		SELECT @body2=@body2+'    ELSE'+ char(13) + char(10)
		SELECT @body2=@body2+'		SELECT ' + char(13) + char(10)
		SELECT @body2=@body2+ @selectcollist + ' FROM (SELECT ' + char(13) + char(10)
		SELECT @body2=@body2+ @selectcollist
		SELECT @body2=@body2+ '        ,row_number() OVER (ORDER BY [' + @column + ']) AS rownum' + char(13)+ char(10)
		SELECT @body2=@body2+ '		FROM ' + @tablename + ') data' + char(13) + char(10) 
		SELECT @body2=@body2+ '		WHERE rownum > @_start AND rownum <= (@_start + @_count) AND ' + @column + ' = @' + @column + ';' + char(13) + char(10)
	END
ELSE
	BEGIN
		SELECT @body2=@body2+'    ELSE'+ char(13) + char(10)
		SELECT @body2=@body2+'		SELECT ' + char(13) + char(10)
		SELECT @body2=@body2+ @selectcollist + ' FROM (SELECT ' + char(13) + char(10)
		SELECT @body2=@body2+ @selectcollist
		SELECT @body2=@body2+ '        ,row_number() OVER (ORDER BY ' + @firstcolumn + ') AS rownum' + char(13)+ char(10)
		SELECT @body2=@body2+ '		FROM ' + @tablename + ') data' + char(13) + char(10) 
		SELECT @body2=@body2+ '		WHERE rownum > @_start AND rownum <= (@_start + @_count);' + char(13) + char(10)
	END

SELECT @body2=@body2+'END' + char(13) + char(10)

-- licence
SET @licence = char(13)+ char(10) + char(13) + char(10) + '-- Generated By AutoProc (http://autoproc.codeplex.com)'

SET @body2 = @body2 + @licence
SET @body = @body1 + @body2
EXEC sp_executesql @body2
SELECT @body

END


